import pymysql
import sys
from AIDetector_pytorch import Detector
from threading import Thread
import cv2
import os
import time
import traceback
import plotline
from flask import jsonify
from retinaface import Retinaface
class mysql_connector():
    def get_connector(self):
        conn=pymysql.connect(host = 'bj-cdb-p748sm9w.sql.tencentcdb.com' # 连接名称，默认127.0.0.1
        ,user = 'root' # 用户名 原来alarmuser
        ,passwd='ACEiverson3' # 密码原来123456
        ,port= 59715 # 端口，默认为3306
        ,db='alarm' # 数据库名称
        ,charset='utf8' # 字符编码
        )
        return conn
    def get_cur(self,conn):
        cur = conn.cursor() # 生成游标对象
        return cur
    def get_alarm_picture(self,cur,type):
        sql1 = "select name,time,picture from " + type
        sql2 = "_violator where picture is not null order by time desc;"
        sql = sql1 + sql2
        print(sql)
        try:
            cur.execute(sql)
            result = cur.fetchall()
        except:
            result = {}
            traceback.print_exc()
        print(result)
        return result
    def insert_violator_id(self,cur,conn,type,name,time,picture):
        sql1 = "insert into " + type 
        sql2 = "_violator(name,time,picture) values(%s,%s,%s)"
        sql = sql1+sql2
        try:
            status = cur.execute(sql,(name,time,picture)) # 执行SQL语句
            conn.commit()
        except:
            traceback.print_exc()
    def insert_violator(self,cur,conn,type,name,time,picture):
        sql = "insert into border_violator(name,time,linetype,picture) values(%s,%s,%s,%s)"
        try:
            cur.execute(sql,(name,time,type,picture)) # 执行SQL语句
            conn.commit()
        except:
            traceback.print_exc()
    def get_table_length(self,cur,conn,type):
        sql1 = "select count(id) from " + type
        sql2 = "_violator;"
        sql = sql1 + sql2
        try:
            cur.execute(sql) # 执行SQL语句
            result = cur.fetchone()
            result = result[0]
        except:
            result = -1
            traceback.print_exc()
        return result
    def get_last_table_length(self,cur,conn,type):
        sql = "select " + type + " from each_table_length"
        try:
            cur.execute(sql) # 执行SQL语句
            result = cur.fetchone()
            result = result[0]
        except:
            result = -1
            traceback.print_exc()
        return result
    
    def update_last_table_length(self,cur,conn,type,length):
        sql = "update each_table_length set " + type + "=" + str(length) + " where id = 1;"
        try:
            cur.execute(sql) # 执行SQL语句
            conn.commit()
        except:
            traceback.print_exc()
        
    def get_violator_id(self,cur,conn,type,num):
        sql1 = "select name from " + type
        sql2 = "_violator order by id desc limit " + str(num) +";"
        sql = sql1 + sql2
        try:
            cur.execute(sql) # 执行SQL语句
            result = cur.fetchall()
        except:
            result = {}
            traceback.print_exc()
        return result
    def get_violator_nameandtime(self,cur,conn,type,num):
        sql1 = "select name,time from " + type
        sql2 = "_violator order by id desc limit " + str(num) +";"
        sql = sql1 + sql2
        try:
            cur.execute(sql) # 执行SQL语句
            result = cur.fetchall()
            print('sqlResult:', result)
        except:
            result = {}
            traceback.print_exc()
        return result
    def get_violator_idandtype(self,cur,conn,type,num):
        sql1 = "select name,linetype,time from " + type
        sql2 = "_violator order by id desc limit " + str(num) +";"
        sql = sql1 + sql2
        try:
            cur.execute(sql) # 执行SQL语句
            result = cur.fetchall()
        except:
            result = {}
            traceback.print_exc()
        return result
    def insert_monitor_border_config(self,cur,conn,monitor_id,linelist,pointlist,draw_index,typelist,linetype):
        print(linetype)
        sql = "insert into monitor_border_config(linelist,pointlist,draw_index,type_list,monitor_id,linetype) value(%s,%s,%s,%s,%s,%s);"
        try:
            status = cur.execute(sql,(linelist,pointlist,draw_index,typelist,monitor_id,linetype)) # 执行SQL语句
            conn.commit()
        except:
            traceback.print_exc()
            status = 0
        return status
    def update_monitor_border_config(self,cur,conn,monitor_id,linelist,pointlist,draw_index,typelist,linetype):
        sql = "update  monitor_border_config set linelist = %s,pointlist = %s, draw_index = %s, type_list = %s,linetype = %s where monitor_id = %s;"
        try:
            status = cur.execute(sql,(linelist,pointlist,draw_index,typelist,linetype,monitor_id)) # 执行SQL语句
            conn.commit()
        except:
            traceback.print_exc()
            status = 0
        return status
    def get_monitor_border_config(self,cur,conn,monitor_id):
        sql = "select linelist,pointlist,draw_index,type_list,linetype from monitor_border_config  where monitor_id = %s;"
        try:
            cur.execute(sql,(monitor_id)) # 执行SQL语句
            result = cur.fetchone()
            conn.commit()
        except:
            traceback.print_exc()
            result = [(),(),(),(),()]
        return result[0],result[1],result[2],result[3],result[4]